﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace ECCIT.Data
{
    internal class OleDbHelper : BaseDBHelper
    {
        /// <summary>
        ///     Build database connection string via related arguments
        /// </summary>
        /// <param name="provider"></param>
        /// <param name="dataSource"></param>
        /// <param name="dbName"></param>
        /// <param name="dbUser"></param>
        /// <param name="dbPassword"></param>
        /// <returns></returns>
        public override string BuildConnectionString(string dataSource, string dbName, string dbUser, string dbPassword)
        {
            string connString = "Provider=Microsoft.Jet.OleDb.4.0;Persist Security Info=False;Data Source={0};User Id={1};password={2}";
            return string.Format(connString, dataSource, dbUser, dbPassword);
        }

        public override IDbConnection GetDbConnection(string connString)
        {
            IDbConnection conn = new OleDbConnection(connString);
            return conn;
        }

        public override IDbCommand GetDbCommand()
        {
            IDbCommand cmd = new OleDbCommand();
            return cmd;
        }

        public override IDataAdapter GetDataAdapter(IDbCommand command)
        {
            IDataAdapter adapter = new OleDbDataAdapter((OleDbCommand)command);
            return adapter;
        }

        public override IDataParameter GetDataParameter(string parameterName, DataType dataType)
        {
            int num1 = GetDataLengthByDataType(dataType);
            return GetDataParameter(parameterName, dataType, num1);
        }

        public override IDataParameter GetDataParameter(string parameterName, DataType dataType, int parameterSize)
        {
            IDataParameter parameter1 = GetDbParameter(parameterName);
            ((OleDbParameter)parameter1).OleDbType = GetOleDbTypeByDataType(dataType);

            int num1 = GetDataLengthByDataType(dataType);
            if (num1 == -1)
                num1 = parameterSize;
            ((OleDbParameter)parameter1).Size = num1;

            return parameter1;
        }

        public override IDataParameter GetDataParameter(string parameterName, DataType dataType, int parameterPrecision, int parameterScale)
        {
            IDataParameter parameter1 = GetDbParameter(parameterName);
            ((OleDbParameter)parameter1).OleDbType = GetOleDbTypeByDataType(dataType);
            ((OleDbParameter)parameter1).Precision = Convert.ToByte(parameterPrecision);
            ((OleDbParameter)parameter1).Scale = Convert.ToByte(parameterScale);

            return parameter1;
        }

        public override IDataParameter GetDbParameter(string parameterName)
        {
            return new OleDbParameter("@" + parameterName, null);
        }

        public override bool TableExists(string tableName)
        {
            bool exists = false;
            using (IDbConnection conn = DBHelper.GetDbConnection())
            {
                try
                {
                    conn.Open();
                    DataTable table = ((OleDbConnection)conn).GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    if (!table.Columns.Contains("TABLE_NAME"))
                        return false;

                    foreach (DataRow dr in table.Rows)
                    {
                        if (dr["TABLE_NAME"].ToString() == tableName)
                        {
                            exists = true;
                            break;
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw new Exception("Error occurred in " + this.GetType().FullName + ".TableExists while checking if table: " + tableName + " exists. Message: " + ex.Message);
                }
                finally
                {
                    if (conn != null && conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return exists;
        }

        public override ResultInfo CreateTable(string tableName, List<Column> columns)
        {
            //StringBuilder builder = new StringBuilder("CREATE TABLE ").Append(tableName).Append(" (");
            //int i = 0;
            //string primaryKey = string.Empty;
            //foreach (Column column in columns)
            //{
            //    string fieldType = fieldInfo.Type;
            //    SystemField systemField = new SystemField();
            //    SystemFieldInfo systemFieldInfo = systemField.GetSysFieldInfo(fieldType);

            //    builder.Append(fieldInfo.PhysicalName).Append(" ").Append(column.);
            //    if (systemFieldInfo.LengthEditable == "1")
            //    {
            //        builder.Append("(").Append(fieldInfo.Length);
            //        if (systemFieldInfo.ScaleEditable == "1")
            //        {
            //            builder.Append(",").Append(fieldInfo.Scale);
            //        }
            //        builder.Append(") ");
            //    }

            //    if (fieldInfo.PhysicalName.ToUpper() == "GUID")
            //    {
            //        primaryKey += (primaryKey == string.Empty) ? fieldInfo.PhysicalName : "," + fieldInfo.PhysicalName;
            //    }

            //    if (fieldInfo.IsNullable == "0")
            //    {
            //        builder.Append(" NOT NULL");
            //    }
            //    else
            //    {
            //        builder.Append(" NULL");
            //    }

            //    builder.Append(",");

            //    if (i == fields.Count - 1 && primaryKey != string.Empty)
            //    {
            //        builder.Append(" CONSTRAINT PK_").Append(tableInfo.PhysicalName).Append(" PRIMARY KEY (").Append(primaryKey).Append(") ");
            //    }

            //    i++;
            //}

            //builder.Append(")");

            //if (CheckTableCreated(guid))
            //    Drop(guid);

            //string cmdCreate = builder.ToString();
            //int rowsAffected = DBHelper.ExecuteNonQuery(cmdCreate);

            //return (rowsAffected == -1) ? true : false;

            //return false;
            return new ResultInfo(true);
        }

        private OleDbType GetOleDbTypeByDataType(DataType dataType)
        {
            switch (dataType)
            {
                case DataType.String:
                        return OleDbType.VarWChar;
                case DataType.Int:
                        return OleDbType.Integer;
                case DataType.Decimal:
                        return OleDbType.Decimal;
                case DataType.NText:
                        return OleDbType.VarWChar;
                case DataType.Date:
                        return OleDbType.DBDate;
                case DataType.Time:
                        return OleDbType.DBTime;
                case DataType.DateTime:
                        return OleDbType.DBTimeStamp;
                case DataType.YearMonth:
                        return OleDbType.VarChar;
                case DataType.Binary:
                        return OleDbType.LongVarBinary;
                case DataType.BigInt:
                    return OleDbType.BigInt;
                case DataType.Bit:
                    return OleDbType.Boolean;
                case DataType.SmallInt:
                    return OleDbType.SmallInt;
                case DataType.TinyInt:
                    return OleDbType.TinyInt;
                case DataType.Guid:
                    return OleDbType.Guid;
                case DataType.TimeStamp:
                    return OleDbType.DBTimeStamp;
            }
            return OleDbType.VarWChar;
        }

        internal class DBFunction : BaseDBHelper.BaseDBFunction
        {
            public override string Length(string columnName)
            {
                return " LEN(" + columnName + ") ";
            }

            public override string Substring(string columnName, int startIndex, int? length)
            {
                string ret = " SUBSTR(" + columnName + "," + startIndex;
                if (length != null)
                    ret += "," + length;
                ret += ") ";
                return ret;
            }

            public override string IsNull(string columnName, object replacementValue)
            {
                string ret = " iif(IsNull(" + columnName + "), '" + replacementValue + "', " + columnName + ") ";
                return ret;
            }

            public override string BooleanEquals(string columnName, bool value)
            {
                string ret = " " + columnName + "=" + value + " ";
                return ret;
            }
        }
    }
}
